import mysql.connector
from mysql.connector import Error
import pandas as pd
from typing import List, Dict, Optional
import getpass


class EmployeeQuery:
    """员工查询类"""

    def __init__(self, host: str = 'localhost', user: str = None,
                 password: str = None, database: str = 'testdb'):
        """
        初始化数据库连接

        Args:
            host: 数据库主机地址
            user: 用户名
            password: 密码
            database: 数据库名
        """
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.cursor = None

    def connect(self) -> bool:
        """
        连接到MySQL数据库

        Returns:
            bool: 连接是否成功
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                collation='utf8mb4_unicode_ci'
            )

            if self.connection.is_connected():
                self.cursor = self.connection.cursor(dictionary=True)
                print(f"成功连接到数据库 '{self.database}'")
                return True

        except Error as e:
            print(f"数据库连接失败: {e}")
            return False

    def disconnect(self):
        """断开数据库连接"""
        if self.connection and self.connection.is_connected():
            if self.cursor:
                self.cursor.close()
            self.connection.close()
            print("数据库连接已关闭")

    def create_sample_employees_table(self):
        """创建示例employees表（如果不存在）"""
        create_table_query = """
                             CREATE TABLE IF NOT EXISTS employees \
                             ( \
                                 id \
                                 INT \
                                 AUTO_INCREMENT \
                                 PRIMARY \
                                 KEY, \
                                 name \
                                 VARCHAR \
                             ( \
                                 100 \
                             ) NOT NULL,
                                 department VARCHAR \
                             ( \
                                 100 \
                             ),
                                 salary DECIMAL \
                             ( \
                                 10, \
                                 2 \
                             ),
                                 hire_date DATE,
                                 email VARCHAR \
                             ( \
                                 255 \
                             )
                                 ) \
                             """

        insert_sample_data_query = """
                                   INSERT \
                                   IGNORE INTO employees (name, department, salary, hire_date, email) VALUES
        ('张三', '技术部', 8500.00, '2020-01-15', 'zhangsan@company.com'),
        ('李四', '销售部', 6500.00, '2019-03-20', 'lisi@company.com'),
        ('王五', '技术部', 12000.00, '2018-06-10', 'wangwu@company.com'),
        ('赵六', '市场部', 7500.00, '2021-02-28', 'zhaoliu@company.com'),
        ('钱七', '技术部', 9500.00, '2020-11-05', 'qianqi@company.com'),
        ('孙八', '人力资源部', 5500.00, '2022-01-10', 'sunba@company.com'),
        ('周九', '销售部', 8000.00, '2019-08-15', 'zhoujiu@company.com'),
        ('吴十', '技术部', 11000.00, '2017-12-01', 'wushi@company.com') \
                                   """

        try:
            # 创建表
            self.cursor.execute(create_table_query)

            # 插入示例数据
            self.cursor.execute(insert_sample_data_query)
            self.connection.commit()

            print("示例employees表创建完成并插入了测试数据")

        except Error as e:
            print(f"创建示例表失败: {e}")
            self.connection.rollback()

    def check_table_exists(self, table_name: str = 'employees') -> bool:
        """检查表是否存在"""
        try:
            self.cursor.execute(f"""
                SELECT COUNT(*) as count 
                FROM information_schema.tables 
                WHERE table_schema = '{self.database}' 
                AND table_name = '{table_name}'
            """)
            result = self.cursor.fetchone()
            return result['count'] > 0
        except Error:
            return False

    def get_employees_above_avg_salary(self) -> Optional[List[Dict]]:
        """
        查询salary高于平均工资的所有员工的name和salary

        Returns:
            查询结果列表或None
        """
        # 方法1: 使用子查询
        query_method1 = """
                        SELECT name, salary, department
                        FROM employees
                        WHERE salary > (SELECT AVG(salary) FROM employees)
                        ORDER BY salary DESC \
                        """

        # 方法2: 使用窗口函数（MySQL 8.0+）
        query_method2 = """
                        SELECT name, salary, department
                        FROM (SELECT name,
                                     salary,
                                     department,
                                     AVG(salary) OVER() as avg_salary \
                              FROM employees) as emp_with_avg
                        WHERE salary > avg_salary
                        ORDER BY salary DESC \
                        """

        try:
            # 使用第一种方法（兼容性更好）
            self.cursor.execute(query_method1)
            results = self.cursor.fetchall()
            return results

        except Error as e:
            print(f"查询执行失败: {e}")
            return None

    def get_average_salary(self) -> Optional[float]:
        """获取平均工资"""
        try:
            self.cursor.execute("SELECT AVG(salary) as avg_salary FROM employees")
            result = self.cursor.fetchone()
            return float(result['avg_salary']) if result['avg_salary'] else 0
        except Error as e:
            print(f"获取平均工资失败: {e}")
            return None

    def get_salary_statistics(self) -> Optional[Dict]:
        """获取工资统计信息"""
        try:
            self.cursor.execute("""
                                SELECT COUNT(*)    as total_employees,
                                       AVG(salary) as avg_salary,
                                       MIN(salary) as min_salary,
                                       MAX(salary) as max_salary,
                                       SUM(salary) as total_salary
                                FROM employees
                                """)
            return self.cursor.fetchone()
        except Error as e:
            print(f"获取统计信息失败: {e}")
            return None

    def get_department_salary_stats(self) -> Optional[List[Dict]]:
        """按部门统计工资信息"""
        try:
            self.cursor.execute("""
                                SELECT department,
                                       COUNT(*)    as employee_count,
                                       AVG(salary) as avg_salary,
                                       MIN(salary) as min_salary,
                                       MAX(salary) as max_salary
                                FROM employees
                                GROUP BY department
                                ORDER BY avg_salary DESC
                                """)
            return self.cursor.fetchall()
        except Error as e:
            print(f"获取部门统计失败: {e}")
            return None

    def get_all_employees(self) -> Optional[List[Dict]]:
        """获取所有员工信息"""
        try:
            self.cursor.execute("""
                                SELECT name, department, salary, hire_date, email
                                FROM employees
                                ORDER BY salary DESC
                                """)
            return self.cursor.fetchall()
        except Error as e:
            print(f"获取员工列表失败: {e}")
            return None


def display_results(results: List[Dict], title: str = "查询结果"):
    """美观地显示查询结果"""
    if not results:
        print("没有找到任何记录")
        return

    print(f"\n{'=' * 70}")
    print(f"{title}")
    print(f"{'=' * 70}")

    # 使用pandas DataFrame来美化显示
    df = pd.DataFrame(results)

    # 格式化数值列
    if 'salary' in df.columns:
        df['salary'] = df['salary'].apply(lambda x: f'¥{x:,.2f}')
    if 'avg_salary' in df.columns:
        df['avg_salary'] = df['avg_salary'].apply(lambda x: f'¥{x:,.2f}')

    print(df.to_string(index=False))
    print(f"\n总计: {len(results)} 条记录")


def display_statistics(stats: Dict):
    """显示统计信息"""
    if not stats:
        return

    print(f"\n{'=' * 50}")
    print("工资统计信息")
    print(f"{'=' * 50}")

    print(f"员工总数: {stats['total_employees']} 人")
    print(f"平均工资: ¥{stats['avg_salary']:,.2f}")
    print(f"最低工资: ¥{stats['min_salary']:,.2f}")
    print(f"最高工资: ¥{stats['max_salary']:,.2f}")
    print(f"工资总额: ¥{stats['total_salary']:,.2f}")


def get_connection_details() -> tuple:
    """获取用户输入的连接详情"""
    print("请输入MySQL数据库连接信息")
    print("(直接按Enter使用默认值)")

    host = input("主机地址 [localhost]: ").strip() or 'localhost'
    user = input("用户名 [root]: ").strip() or 'root'
    password = getpass.getpass("密码: ")
    database = input("数据库名 [testdb]: ").strip() or 'testdb'

    return host, user, password, database


def main():
    """主函数"""
    print("员工工资查询系统")
    print("=" * 60)
    print("功能: 查询salary高于平均工资的员工")
    print("=" * 60)

    # 获取连接信息
    host, user, password, database = get_connection_details()

    # 创建查询实例
    query_tool = EmployeeQuery(host, user, password, database)

    try:
        # 连接数据库
        if not query_tool.connect():
            return

        # 检查employees表是否存在，如果不存在则创建示例表
        if not query_tool.check_table_exists('employees'):
            print("employees表不存在，是否创建示例表？ (y/n)")
            choice = input().strip().lower()
            if choice == 'y':
                query_tool.create_sample_employees_table()
            else:
                print("操作取消")
                return

        while True:
            print(f"\n{'=' * 60}")
            print("请选择查询选项:")
            print("1. 查询工资高于平均工资的员工")
            print("2. 查看所有员工信息")
            print("3. 查看工资统计信息")
            print("4. 查看部门工资统计")
            print("5. 退出")
            print(f"{'=' * 60}")

            choice = input("请输入选项 (1-5): ").strip()

            if choice == '1':
                # 获取平均工资
                avg_salary = query_tool.get_average_salary()
                if avg_salary is not None:
                    print(f"\n公司平均工资: ¥{avg_salary:,.2f}")

                # 查询高于平均工资的员工
                high_salary_employees = query_tool.get_employees_above_avg_salary()

                if high_salary_employees:
                    display_results(
                        high_salary_employees,
                        f"工资高于平均工资(¥{avg_salary:,.2f})的员工"
                    )

                    # 显示额外信息
                    high_salary_count = len(high_salary_employees)
                    total_stats = query_tool.get_salary_statistics()
                    if total_stats:
                        total_employees = total_stats['total_employees']
                        percentage = (high_salary_count / total_employees) * 100
                        print(f"\n{high_salary_count}/{total_employees} 名员工工资高于平均 ({percentage:.1f}%)")
                else:
                    print("无法获取高工资员工数据")

            elif choice == '2':
                # 显示所有员工
                all_employees = query_tool.get_all_employees()
                if all_employees:
                    display_results(all_employees, "所有员工信息")
                else:
                    print("无法获取员工数据")

            elif choice == '3':
                # 显示统计信息
                stats = query_tool.get_salary_statistics()
                if stats:
                    display_statistics(stats)
                else:
                    print("无法获取统计信息")

            elif choice == '4':
                # 显示部门统计
                dept_stats = query_tool.get_department_salary_stats()
                if dept_stats:
                    display_results(dept_stats, "各部门工资统计")
                else:
                    print("无法获取部门统计信息")

            elif choice == '5':
                print("再见！")
                break
            else:
                print("无效选项，请重新选择")

    except KeyboardInterrupt:
        print("\n程序被用户中断")
    except Exception as e:
        print(f"\n发生错误: {e}")
    finally:
        # 确保断开连接
        query_tool.disconnect()


def show_sql_queries():
    """显示使用的SQL查询语句"""
    print("\n" + "=" * 60)
    print("使用的SQL查询语句")
    print("=" * 60)

    # 主要查询语句
    main_query = """
                 -- 方法1: 使用子查询（推荐，兼容性好）
                 SELECT name, salary, department
                 FROM employees
                 WHERE salary > (SELECT AVG(salary) FROM employees)
                 ORDER BY salary DESC;

                 -- 方法2: 使用窗口函数（MySQL 8.0+）
                 SELECT name, salary, department
                 FROM (SELECT name,
                              salary,
                              department,
                              AVG(salary) OVER() as avg_salary \
                       FROM employees) as emp_with_avg
                 WHERE salary > avg_salary
                 ORDER BY salary DESC;

                 -- 获取平均工资
                 SELECT AVG(salary) as avg_salary \
                 FROM employees;

                 -- 获取详细统计信息
                 SELECT COUNT(*)    as total_employees, \
                        AVG(salary) as avg_salary, \
                        MIN(salary) as min_salary, \
                        MAX(salary) as max_salary, \
                        SUM(salary) as total_salary
                 FROM employees;

                 -- 按部门统计
                 SELECT department, \
                        COUNT(*)    as employee_count, \
                        AVG(salary) as avg_salary, \
                        MIN(salary) as min_salary, \
                        MAX(salary) as max_salary
                 FROM employees
                 GROUP BY department
                 ORDER BY avg_salary DESC; \
                 """

    print(main_query)


if __name__ == "__main__":
    # 安装提示
    try:
        import mysql.connector
    except ImportError:
        print("未找到mysql-connector-python库")
        print("请使用以下命令安装:")
        print("pip install mysql-connector-python")
        exit(1)

    try:
        import pandas
    except ImportError:
        print("未找到pandas库，显示格式将简化")
        print("可以使用: pip install pandas 安装")

    # 运行主程序
    main()

    # 显示SQL查询语句
    show_sql_queries()
